use rusqlite::{Connection, Result as SqliteResult};
use crate::database::models::{Model, SortOption, PaginationOption, QueryResult};

/// 查询构建器
#[derive(Debug, Clone)]
pub struct QueryBuilder {
    table: String,
    select_fields: Vec<String>,
    where_conditions: Vec<WhereCondition>,
    joins: Vec<JoinClause>,
    sort_options: Vec<SortOption>,
    pagination: Option<PaginationOption>,
    group_by: Vec<String>,
    having_conditions: Vec<WhereCondition>,
}

/// WHERE 条件
#[derive(Debug, Clone)]
pub struct WhereCondition {
    pub field: String,
    pub operator: WhereOperator,
    pub value: QueryValue,
    pub connector: WhereConnector,
}

/// WHERE 操作符
#[derive(Debug, Clone)]
pub enum WhereOperator {
    Equal,
    NotEqual,
    GreaterThan,
    GreaterThanOrEqual,
    LessThan,
    LessThanOrEqual,
    Like,
    NotLike,
    In,
    NotIn,
    IsNull,
    IsNotNull,
    Between,
}

impl WhereOperator {
    pub fn to_sql(&self) -> &'static str {
        match self {
            WhereOperator::Equal => "=",
            WhereOperator::NotEqual => "!=",
            WhereOperator::GreaterThan => ">",
            WhereOperator::GreaterThanOrEqual => ">=",
            WhereOperator::LessThan => "<",
            WhereOperator::LessThanOrEqual => "<=",
            WhereOperator::Like => "LIKE",
            WhereOperator::NotLike => "NOT LIKE",
            WhereOperator::In => "IN",
            WhereOperator::NotIn => "NOT IN",
            WhereOperator::IsNull => "IS NULL",
            WhereOperator::IsNotNull => "IS NOT NULL",
            WhereOperator::Between => "BETWEEN",
        }
    }
}

/// WHERE 连接符
#[derive(Debug, Clone)]
pub enum WhereConnector {
    And,
    Or,
}

impl WhereConnector {
    pub fn to_sql(&self) -> &'static str {
        match self {
            WhereConnector::And => "AND",
            WhereConnector::Or => "OR",
        }
    }
}

/// 查询值
#[derive(Debug, Clone)]
pub enum QueryValue {
    String(String),
    Integer(i64),
    Float(f64),
    Boolean(bool),
    Null,
    List(Vec<QueryValue>),
    Range(Box<QueryValue>, Box<QueryValue>),
}

/// JOIN 子句
#[derive(Debug, Clone)]
pub struct JoinClause {
    pub join_type: JoinType,
    pub table: String,
    pub on_condition: String,
}

/// JOIN 类型
#[derive(Debug, Clone)]
pub enum JoinType {
    Inner,
    Left,
    Right,
    Full,
}

impl JoinType {
    pub fn to_sql(&self) -> &'static str {
        match self {
            JoinType::Inner => "INNER JOIN",
            JoinType::Left => "LEFT JOIN",
            JoinType::Right => "RIGHT JOIN",
            JoinType::Full => "FULL OUTER JOIN",
        }
    }
}

impl QueryBuilder {
    /// 创建新的查询构建器
    pub fn new(table: &str) -> Self {
        Self {
            table: table.to_string(),
            select_fields: vec!["*".to_string()],
            where_conditions: Vec::new(),
            joins: Vec::new(),
            sort_options: Vec::new(),
            pagination: None,
            group_by: Vec::new(),
            having_conditions: Vec::new(),
        }
    }

    /// 为模型创建查询构建器
    pub fn for_model<T: Model>() -> Self {
        Self::new(T::table_name())
    }

    /// 选择字段
    pub fn select(mut self, fields: &[&str]) -> Self {
        self.select_fields = fields.iter().map(|s| s.to_string()).collect();
        self
    }

    /// 添加 WHERE 条件
    pub fn where_eq(mut self, field: &str, value: QueryValue) -> Self {
        self.where_conditions.push(WhereCondition {
            field: field.to_string(),
            operator: WhereOperator::Equal,
            value,
            connector: WhereConnector::And,
        });
        self
    }

    /// 添加 WHERE LIKE 条件
    pub fn where_like(mut self, field: &str, pattern: &str) -> Self {
        self.where_conditions.push(WhereCondition {
            field: field.to_string(),
            operator: WhereOperator::Like,
            value: QueryValue::String(pattern.to_string()),
            connector: WhereConnector::And,
        });
        self
    }

    /// 添加 WHERE IN 条件
    pub fn where_in(mut self, field: &str, values: Vec<QueryValue>) -> Self {
        self.where_conditions.push(WhereCondition {
            field: field.to_string(),
            operator: WhereOperator::In,
            value: QueryValue::List(values),
            connector: WhereConnector::And,
        });
        self
    }

    /// 添加 OR WHERE 条件
    pub fn or_where_eq(mut self, field: &str, value: QueryValue) -> Self {
        self.where_conditions.push(WhereCondition {
            field: field.to_string(),
            operator: WhereOperator::Equal,
            value,
            connector: WhereConnector::Or,
        });
        self
    }

    /// 添加排序
    pub fn order_by(mut self, field: &str, direction: crate::database::models::SortDirection) -> Self {
        self.sort_options.push(SortOption::new(field, direction));
        self
    }

    /// 添加分页
    pub fn paginate(mut self, page: usize, per_page: usize) -> Self {
        self.pagination = Some(PaginationOption::new(page, per_page));
        self
    }

    /// 添加 INNER JOIN
    pub fn inner_join(mut self, table: &str, on_condition: &str) -> Self {
        self.joins.push(JoinClause {
            join_type: JoinType::Inner,
            table: table.to_string(),
            on_condition: on_condition.to_string(),
        });
        self
    }

    /// 添加 LEFT JOIN
    pub fn left_join(mut self, table: &str, on_condition: &str) -> Self {
        self.joins.push(JoinClause {
            join_type: JoinType::Left,
            table: table.to_string(),
            on_condition: on_condition.to_string(),
        });
        self
    }

    /// 构建 SQL 查询
    pub fn build_select(&self) -> (String, Vec<QueryValue>) {
        let mut sql = format!("SELECT {} FROM {}", 
            self.select_fields.join(", "), 
            self.table
        );
        let mut params = Vec::new();

        // 添加 JOIN
        for join in &self.joins {
            sql.push_str(&format!(" {} {} ON {}", 
                join.join_type.to_sql(), 
                join.table, 
                join.on_condition
            ));
        }

        // 添加 WHERE 条件
        if !self.where_conditions.is_empty() {
            sql.push_str(" WHERE ");
            for (i, condition) in self.where_conditions.iter().enumerate() {
                if i > 0 {
                    sql.push_str(&format!(" {} ", condition.connector.to_sql()));
                }
                
                match &condition.operator {
                    WhereOperator::IsNull | WhereOperator::IsNotNull => {
                        sql.push_str(&format!("{} {}", condition.field, condition.operator.to_sql()));
                    }
                    WhereOperator::In | WhereOperator::NotIn => {
                        if let QueryValue::List(values) = &condition.value {
                            let placeholders = vec!["?"; values.len()].join(", ");
                            sql.push_str(&format!("{} {} ({})", 
                                condition.field, 
                                condition.operator.to_sql(), 
                                placeholders
                            ));
                            params.extend(values.clone());
                        }
                    }
                    WhereOperator::Between => {
                        if let QueryValue::Range(start, end) = &condition.value {
                            sql.push_str(&format!("{} BETWEEN ? AND ?", condition.field));
                            params.push((**start).clone());
                            params.push((**end).clone());
                        }
                    }
                    _ => {
                        sql.push_str(&format!("{} {} ?", condition.field, condition.operator.to_sql()));
                        params.push(condition.value.clone());
                    }
                }
            }
        }

        // 添加 ORDER BY
        if !self.sort_options.is_empty() {
            sql.push_str(" ORDER BY ");
            let order_clauses: Vec<String> = self.sort_options
                .iter()
                .map(|sort| format!("{} {}", sort.field, sort.direction.to_sql()))
                .collect();
            sql.push_str(&order_clauses.join(", "));
        }

        // 添加 LIMIT 和 OFFSET
        if let Some(pagination) = &self.pagination {
            sql.push_str(&format!(" LIMIT {} OFFSET {}", 
                pagination.limit(), 
                pagination.offset()
            ));
        }

        (sql, params)
    }

    /// 执行查询并返回结果
    pub fn execute<T: Model>(&self, conn: &Connection) -> SqliteResult<QueryResult<T>> {
        let (sql, params) = self.build_select();
        
        // 转换参数为 rusqlite 可用的格式
        let rusqlite_params: Vec<&dyn rusqlite::ToSql> = params
            .iter()
            .map(|p| p as &dyn rusqlite::ToSql)
            .collect();

        let mut stmt = conn.prepare(&sql)?;
        let rows = stmt.query_map(&rusqlite_params[..], |row| T::from_row(row))?;

        let mut results = Vec::new();
        for row in rows {
            results.push(row?);
        }

        // 如果有分页，计算总数
        if let Some(pagination) = &self.pagination {
            let count_sql = format!("SELECT COUNT(*) FROM {}", self.table);
            let total: usize = conn.query_row(&count_sql, [], |row| row.get(0))?;
            
            Ok(QueryResult::with_pagination(
                results, 
                total, 
                pagination.page, 
                pagination.per_page
            ))
        } else {
            Ok(QueryResult::new(results))
        }
    }
}

// 实现 ToSql trait 为 QueryValue
impl rusqlite::ToSql for QueryValue {
    fn to_sql(&self) -> rusqlite::Result<rusqlite::types::ToSqlOutput<'_>> {
        match self {
            QueryValue::String(s) => s.to_sql(),
            QueryValue::Integer(i) => i.to_sql(),
            QueryValue::Float(f) => f.to_sql(),
            QueryValue::Boolean(b) => b.to_sql(),
            QueryValue::Null => Ok(rusqlite::types::ToSqlOutput::Owned(rusqlite::types::Value::Null)),
            QueryValue::List(_) => Err(rusqlite::Error::ToSqlConversionFailure(
                Box::new(std::fmt::Error)
            )),
            QueryValue::Range(_, _) => Err(rusqlite::Error::ToSqlConversionFailure(
                Box::new(std::fmt::Error)
            )),
        }
    }
}
